package com.junsheng.print.util;

import java.io.File;

import com.junsheng.print.entity.dto.AccountsReceivableAging;
import com.junsheng.print.entity.dto.Factoring;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.DecimalFormat;
import java.util.*;
import java.util.stream.Collectors;

public class ExcelUtil {

    public static List<AccountsReceivableAging> read() {
        String filePath = "C:\\Users\\Mayn\\Desktop\\账龄.xlsx";
        try (FileInputStream fis = new FileInputStream(new File(filePath));
             Workbook workbook = new XSSFWorkbook(fis)) {

            List<AccountsReceivableAging> allData = new ArrayList<>();
            Sheet sheet = workbook.getSheetAt(0); // 读取第一个 sheet（索引从 0 开始）
            int rowFlag = 0;
            int columnFlag = 0;
            for (Row row : sheet) {
                AccountsReceivableAging rowData = new AccountsReceivableAging();
                List<BigDecimal> amountList = new ArrayList<>();
                if (rowFlag == 0) {
                    rowFlag++;
                    continue;
                }
                for (Cell cell : row) {
                    switch (cell.getCellType()) {
                        case STRING:
                            if (columnFlag == 0) {
//                                System.out.print(cell.getStringCellValue() + "\t");
//                            System.out.print(cell.getStringCellValue() +rowFlag+columnFlag+ "\t");
//                                System.out.println(columnFlag+"列");
                                String stringCellValue = cell.getStringCellValue();
                                rowData.setCustomerCode(stringCellValue);
                            }else if (columnFlag == 1) {
//                                System.out.print(cell.getStringCellValue() + "\t");
//                                System.out.println(columnFlag+"列");
                                String stringCellValue = cell.getStringCellValue();
                                rowData.setCustomerName(stringCellValue);
                            }
                            columnFlag++;
                            break;
                        case NUMERIC:
                            if (columnFlag == 0) {
//                                System.out.print(cell.getStringCellValue() + "\t");
//                            System.out.print(cell.getStringCellValue() +rowFlag+columnFlag+ "\t");
//                                System.out.println(columnFlag+"列");
                                DecimalFormat formats = new DecimalFormat("0");
                                String customerCode = formats.format(cell.getNumericCellValue());
                                rowData.setCustomerCode(customerCode);
                                continue;
                            }
                            if (DateUtil.isCellDateFormatted(cell)) {
//                                System.out.print(cell.getDateCellValue() + "\t");
                            } else {
//                                System.out.print(cell.getNumericCellValue() + "\t");
                                //走的这个数据
//                                BigDecimal currentAmount = new BigDecimal(cell.getNumericCellValue());
//                                amountList.add(currentAmount);
                                BigDecimal currentAmount;

                                if (cell.getCellType() == CellType.NUMERIC) {
                                    // 避免 double 精度误差并保留两位小数
                                    currentAmount = BigDecimal.valueOf(cell.getNumericCellValue()).setScale(2, RoundingMode.HALF_UP);
                                } else if (cell.getCellType() == CellType.STRING) {
                                    // 如果是文本，也兼容，并保留两位小数
                                    currentAmount = new BigDecimal(cell.getStringCellValue()).setScale(2, RoundingMode.HALF_UP);
                                } else {
                                    currentAmount = BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP);
                                }
                                if (amountList.size() > 16) {
                                    continue;
                                }
                                amountList.add(currentAmount);


                            }
                            break;
                    }
                    rowData.setAmountList(amountList);
                }
                columnFlag = 0;
                allData.add(rowData);
            }


            Iterator<AccountsReceivableAging> iterator = allData.iterator();
            while (iterator.hasNext()) {
                AccountsReceivableAging next = iterator.next();
                if (next.getCustomerCode()==null||next.getAmountList().isEmpty()) {
                    iterator.remove();
                }
            }
            for (AccountsReceivableAging dataItem : allData) {
                System.out.println(dataItem);
            }

            rowFlag = 0;
            columnFlag = 0;
            //读取第三个sheet
            List<Factoring> factoringList = new ArrayList<>();
            //读取sheet2:合同负债
            sheet = workbook.getSheetAt(1);
            for (Row row : sheet) {
                //第一行放行
                if (rowFlag == 0||rowFlag==17) {
                    rowFlag++;
                    continue;
                }
                Factoring factoring = new Factoring();
                for (Cell cell : row) {
                    switch (cell.getCellType()) {
                        case STRING:
//                            System.out.print("String ");
//                            System.out.print(cell.getStringCellValue() + "\t");
                            if (columnFlag == 0) {
                                //第一列
                                factoring.setCustomerCode(cell.getStringCellValue());
                            } else if (columnFlag == 1) {
                                factoring.setCustomerName(cell.getStringCellValue());
                            }
                            break;
                        case NUMERIC:
                            if (columnFlag == 0) {
                                DecimalFormat formats = new DecimalFormat("0");
                                String customerCode = formats.format(cell.getNumericCellValue());
                                //第一列
                                factoring.setCustomerCode(customerCode);
                                continue;
                            }
                            if (DateUtil.isCellDateFormatted(cell)) {
//                                System.out.print(cell.getDateCellValue() + "\t");
                            } else {
//                                System.out.print("NUMERIC ");
//                                System.out.print(cell.getNumericCellValue() + "\t");
                                BigDecimal amount;
                                if (cell.getCellType() == CellType.NUMERIC) {
                                    amount = BigDecimal.valueOf(cell.getNumericCellValue()).setScale(2, RoundingMode.HALF_UP);
                                } else if (cell.getCellType() == CellType.STRING) {
                                    amount = new BigDecimal(cell.getStringCellValue()).setScale(2, RoundingMode.HALF_UP);
                                } else {
                                    amount = BigDecimal.ZERO.setScale(2, RoundingMode.HALF_UP);
                                }
                                factoring.setAmount(amount);

                            }
                    }
                    columnFlag++;
                }
                factoringList.add(factoring);
                columnFlag = 0;
            }
            for (Factoring factoring : factoringList) {
                System.out.println(factoring);
            }

            //factoringList根据code分成map对象
            Map<String, Factoring> factoringMap = factoringList.stream().collect(Collectors.toMap(Factoring::getCustomerCode, factoring -> factoring));

            //循环allData
            for (AccountsReceivableAging dataItem : allData) {
                String customerCode = dataItem.getCustomerCode();
                Factoring factoring = factoringMap.get(customerCode);
                if (factoring == null) {
                    continue;
                }

                //
                BigDecimal allAmount = factoring.getAmount();
                List<BigDecimal> amountList = dataItem.getAmountList();

                for (int i = amountList.size() - 1; i >= 0; i--) {
                    //拿出当前金额
                    BigDecimal currentAmount = amountList.get(i);
//                    System.out.println("当前客户总金额"+allAmount);
                    //如果当前金额小于0，则跳过
                    if (currentAmount.compareTo(BigDecimal.ZERO) <= 0) {
                        //newAmountList.add(currentAmount);
                        amountList.set(i, currentAmount);
                        continue;
                    }
                    //如果总金额为0，则跳过
                    if (allAmount.compareTo(BigDecimal.ZERO) == 0) {
                        amountList.set(i, currentAmount);
                        continue;
                    }
                    //如果当前金额大于0，比较总金额和当前金额谁大
                    if (allAmount.compareTo(currentAmount) >= 0) {
                        //当前金额比总金额小，总金额减去当前金额，设置当前金额为0
                        allAmount = allAmount.subtract(currentAmount);
                        currentAmount = BigDecimal.ZERO;
                        amountList.set(i, currentAmount);
                        //newAmountList.add(currentAmount);
                    } else if (allAmount.compareTo(currentAmount) < 0) {
                        //当前金额比总金额大，当前金额减去总金额
                        currentAmount = currentAmount.subtract(allAmount);
                        amountList.set(i, currentAmount);
                        allAmount = BigDecimal.ZERO;
                        //newAmountList.add(currentAmount);
                    }

                }

            }
            System.out.println("=======修改后=======");
            for (AccountsReceivableAging dataItem : allData) {
                System.out.println(dataItem);
            }

            return allData;

        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static void main(String[] args) {
        write(read());
    }

    public static void write(List<AccountsReceivableAging> resultList) {
        List<String> titleHead = Arrays.asList(
                "客户代码", "客户名称", "30天内", "31-60天内", "61-90天内", "91-120天内",
                "121-150天内", "151-180天内", "181-210天内", "211-240天内",
                "241-270天内", "271-300天内", "301-330天内", "331-365天",
                "366-730天内", "731-1095天内", "1096-1460天内", "1461-1825天内",
                "1826天后"
        );

        //创建Workbook实例，对应一个Excel文件
        try (Workbook workbook = new XSSFWorkbook()) { // 使用try-with-resources自动关闭Workbook
            // 在Workbook中添加一个sheet，对应Excel文件中的sheet
            Sheet sheet = workbook.createSheet("应收账龄");

            // TODO 创建字体样式（仿宋）
            Font font = workbook.createFont();
            font.setFontName("仿宋"); // 设置字体为仿宋
            font.setFontHeightInPoints((short) 20); // 设置字体大小

            // TODO 创建单元格样式
            CellStyle style = workbook.createCellStyle();
            style.setFont(font);
            font.setFontName("仿宋_GB2312");
            font.setBold(true);
            style.setAlignment(HorizontalAlignment.CENTER); // 水平居中
            style.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中

            // 创建行（Row），参数0表示第一行
            Row row = sheet.createRow(0);
            //设置行高

            // 在行（Row）上创建单元格（Cell），参数0表示第一列
            for (int i = 0; i < titleHead.size(); i++) {
                Cell cell = row.createCell(i);
                cell.setCellValue(titleHead.get(i)); // 设置单元格的值
                //TODO 设置行宽
                if (i == 1) {
                    sheet.setColumnWidth(i, 30 * 256);
                }else {
                    sheet.setColumnWidth(i, 15 * 256);
                }
            }

            int startRow = 1;
            for (AccountsReceivableAging accountsReceivableAging : resultList) {
                //创建行
                row = sheet.createRow(startRow);
                //创建列
                for (int i = 0; i < titleHead.size(); i++) {
                    Cell cell = row.createCell(i);
                    if (i == 0) {
                        cell.setCellValue(accountsReceivableAging.getCustomerCode()); // 设置单元格的值
                    } else if (i == 1) {
                        cell.setCellValue(accountsReceivableAging.getCustomerName());
                    }else {
                        BigDecimal bigDecimal = accountsReceivableAging.getAmountList().get(i - 2);
                        cell.setCellValue(bigDecimal.toString());
                    }
                }
                startRow++;
            }
            // 写入到文件系统中的文件
            try (FileOutputStream outputStream = new FileOutputStream("应收账龄.xlsx")) {
                workbook.write(outputStream);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}